package WebServiceDBController;

import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import WebServiceModel.*;

public class GamingStateDB {
	
    public static GamingStateDB game=new GamingStateDB();
    private static Connection con=null;
    private static PreparedStatement stmt=null;        
    private static String Driver="com.mysql.jdbc.Driver";
    private static String url="jdbc:mysql://localhost:3306/GameUser";
    private static String gameuser="root";
    private static String password="586088";
    private static String sql;
    
    public GamingStateDB(){}
    
    public GamingStateModel GetState(GamingStateModel state){    	
    	 		
            ResultSet rs=null;
           
            try{ 
                Class.forName(Driver); 
            } catch(Exception e){ 
                System.out.println("�޷����������" +Driver); 
            } 

            try{           
                con=DriverManager.getConnection(url,gameuser,password);                
                sql="select * from gamingstate where RoomID=? and DeskID=?";                            
                stmt=con.prepareStatement(sql);  
                
                stmt.setInt(1, state.getRoomID());
                stmt.setInt(2, state.getDeskID());                     
                rs=stmt.executeQuery();                
                
                if(rs.next()){                  
                    
                    state.setState(rs.getInt("State"));
                    state.setPos(rs.getInt("PosX"), rs.getInt("PosY")); 
                    state.setTime(rs.getInt("Time"));
                }
                rs.close();
                if(!con.isClosed()) 
                    System.out.println("��ѯ�ɹ�"); 
                con.close(); 
                return state;
            } 
            catch(SQLException ee){             
               System.out.println("��ѯʧ��");
            }   
            
            return null;  	
    	
    }
    
    public void ClearOption(GamingStateModel state){
    	
    	try{ 

            Class.forName(Driver); 

        } 

        catch(Exception e){ 

            System.out.println("�޷����������" +Driver); 

        } 

        try{           
 
            con=DriverManager.getConnection(url,gameuser,password); 
            
            sql="delete from gamingstate where RoomID=? and DeskID=?";             
            
            stmt=con.prepareStatement(sql);
            
            stmt.setInt(1, state.getRoomID());
            stmt.setInt(2, state.getDeskID());                 
            
            stmt.executeUpdate();           
   
            if(!con.isClosed()) 

                System.out.println("ɾ��ɹ�"); 

            con.close(); 

        } 
        catch(SQLException ee){             
           System.out.println("ɾ��ʧ��");
        }
    }
    
    public void ModifyOption(GamingStateModel state){
    	
    	try{ 

            Class.forName(Driver); 

        } 

        catch(Exception e){ 

            System.out.println("�޷����������" +Driver); 

        } 

        try{           
 
            con=DriverManager.getConnection(url,gameuser,password); 
            
            sql="update gamingstate set State=? ,PosX=? ,PosY=? ,Time=? where RoomID=? and DeskID=?";             
            
            stmt=con.prepareStatement(sql);            
            
            stmt.setInt(1, state.getState());
            stmt.setInt(2, state.getPos().x);
            stmt.setInt(3, state.getPos().y);
            stmt.setInt(4, state.getTime());
            stmt.setInt(5, state.getRoomID());
            stmt.setInt(6, state.getDeskID());
            
            
                  
            stmt.executeUpdate();          

            if(!con.isClosed()) 

                System.out.println("�޸ĳɹ�"); 

            con.close(); 

        } 
        catch(SQLException ee){            
            
           System.out.println("�޸�ʧ��");
        }
    }
    
    public void InsertOption(GamingStateModel state){
    	
    	try{ 

            Class.forName(Driver); 

        } 

        catch(Exception e){ 

            System.out.println("�޷����������" +Driver); 

        } 

        try{           
 
            con=DriverManager.getConnection(url,gameuser,password); 
            
            sql="insert into gamingstate (RoomID,DeskID ,State ,PosX ,PosY ,Time) values (?,?,?,?,?,?)";             
            
            stmt=con.prepareStatement(sql);
            
            stmt.setInt(1, state.getRoomID());
            stmt.setInt(2, state.getDeskID());
            stmt.setInt(3, state.getState());
            stmt.setInt(4, state.getPos().x);
            stmt.setInt(5, state.getPos().y);   
            stmt.setInt(6, state.getTime());
                  
            stmt.executeUpdate();           

            if(!con.isClosed()) 

                System.out.println("��ӳɹ�"); 

            con.close(); 

        } 
        catch(SQLException ee){ 
           System.out.println("���ʧ��");
        }
    	
    }
    
    
    
    public static GamingStateDB getInstance(){
    	
    	return game;
    }
    
    public void createTable(){
    	
    	Statement stmt=null;
        
        try{ 

           Class.forName(Driver); 

       } 

       catch(Exception e){ 

           System.out.println(e.toString()); 

       } 

       try{           

           con=DriverManager.getConnection(url,gameuser,password); 
           stmt=con.createStatement();
           sql="create table gamingstate(RoomID int(10),DeskID int(20),State int(10),PosX int(20),PosY int(20), Time int(255))";            
           stmt.execute(sql);           

           if(!con.isClosed()) 

               System.out.println("successful"); 

           con.close(); 

       } 
       catch(SQLException ee){ 
          System.out.println(ee.toString());
       }    
    }
    
    
    /*public static void main(String args[]){
    	GamingStateDB temp=GamingStateDB.getInstance();    	
    	//temp.createTable();
    	GamingStateModel state=GamingStateModel.getInstance();
    	
    	temp.createTable();
    	
    }*/
    
}
